﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using EZCRS.HCMS.Data;

namespace EZCRS.HCMS.SqlClient
{
    public class sql_AdmCardUser:MainBase
    {
        private string SqlStr = "";

        /// <summary>
        /// 更新常客可用积分或冻结积分总数
        /// </summary>
        /// <param name="uid"></param>
        /// <param name="types">1=可用积分；2=冻结积分；其他1、2都更新</param>
        public static void statscore(int uid, int types)
        {
            SqlParameter[] pam = new SqlParameter[2];
            pam[0] = new SqlParameter("@uid", uid);
            pam[1] = new SqlParameter("@type", types);
            ExecSql.SqlExecNoqueryByProcedure("hfg_updateuserscore", pam);
        }

        #region 常客列表
        public DataTable CardUser_List(string StrWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select A.*,B.mobnumb,C.email,D.username,E.cardnumber from hfg_user A left join  hfg_user_mobile B on A.uid=B.uid");
            strSql.Append(" left join  hfg_user_email C on A.uid=C.uid ");
            strSql.Append(" left join  hfg_user_username D on A.uid=D.uid ");
            strSql.Append(" left join  hfg_user_cardnumber E on A.uid=E.uid where 1=1 ");
            if (StrWhere.Trim() != "")
                strSql.Append(StrWhere);
            return ExecSql.GetDataSet(strSql.ToString()).Tables[0];
        }
        #endregion

        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet CardUser_List(int PageSize, int PageIndex, string StrWhere, string ViewName)
        {
            return ExecSql.AddParamFrKeyField(ViewName, "*", "regdate", "uid", PageSize, PageIndex, StrWhere);
        }
        #region 常客Info数据的显示,显示一条数据
        public DataRow CardUser_Item(string UId, int GId)
        {
            try
            {
                return ExecSql.GetDataSet("select * from hfg_user where uid=" + UId + " and gid=" + GId.ToString()).Tables[0].Rows[0];
            }
            catch
            {
                return null;
            }
        }
        #endregion

        #region 常客Info数据的,以及常客和其他有关联的几张表的数据的同时显示,显示一条数据
        public DataSet CardUser_Item2(string UId, int GId)
        {
            try
            {
                string strSql = "select * from hfg_user where uid=" + UId + " and gid=" + GId.ToString() + ";";
                strSql = strSql + "select * from hfg_user_cardnumber where uid=" + UId + " and gid=" + GId.ToString() + ";";
                strSql = strSql + "select * from hfg_user_username where uid=" + UId + " and gid=" + GId.ToString() + ";";
                strSql = strSql + "select * from hfg_user_email where uid=" + UId + " and gid=" + GId.ToString() + ";";
                strSql = strSql + "select * from hfg_user_mobile where uid=" + UId + " and gid=" + GId.ToString();
                return ExecSql.GetDataSet(strSql);
            }
            catch
            {
                return null;
            }
        }
        #endregion

        #region 常客信息添加、修改
        /// <summary>
        /// 修改或添加常客信息
        /// </summary>
        /// <returns></returns>
        public bool CardUser_Add(string Lvlid, string Endyear, string Scores, string Emailformat, string Emaillanguage, string Usrfirstname, string Usrlastname, string Usrgender, string Usrbirthday, string Usrnationality, string Idtype, string Idno, string Usrcountry, string Usrprovince, string Usrcity, string Smoke, string Bed, string Pillow, string Usrstatus, string Ademail, string Admobile, string Uid)
        {
            SqlParameter[] pam = new SqlParameter[21];
            pam[0] = new SqlParameter("@lvlid", Lvlid);
            pam[1] = new SqlParameter("@endyear", Endyear);
            pam[2] = new SqlParameter("@scores", Scores);
            pam[3] = new SqlParameter("@emailformat", Emailformat);
            pam[4] = new SqlParameter("@emaillanguage", Emaillanguage);
            pam[5] = new SqlParameter("@usrfirstname", Usrfirstname);
            pam[6] = new SqlParameter("@usrlastname", Usrlastname);
            pam[7] = new SqlParameter("@usrgender", Usrgender);
            pam[8] = new SqlParameter("@usrbirthday", Usrbirthday);
            pam[9] = new SqlParameter("@usrnationality", Usrnationality);
            pam[10] = new SqlParameter("@idtype", Idtype);
            pam[11] = new SqlParameter("@idno", Idno);
            pam[12] = new SqlParameter("@usrcountry", Usrcountry);
            pam[13] = new SqlParameter("@usrprovince", Usrprovince);
            pam[14] = new SqlParameter("@usrcity", Usrcity);
            pam[15] = new SqlParameter("@smoke", Smoke);
            pam[16] = new SqlParameter("@bed", Bed);
            pam[17] = new SqlParameter("@pillow", Pillow);
            pam[18] = new SqlParameter("@usrstatus", Usrstatus);
            pam[19] = new SqlParameter("@ademail", Ademail);
            pam[20] = new SqlParameter("@admobile", Admobile);
            if (Uid != "")
                SqlStr = "update hfg_user set lvlid=@lvlid,endyear=@endyear,scores=@scores,emailformat=@emailformat,emaillanguage=@emaillanguage,usrfirstname=@usrfirstname,usrlastname=@usrlastname,usrgender=@usrgender,usrbirthday=@usrbirthday,usrnationality=@usrnationality,idtype=@idtype,idno=@idno,usrcountry=@usrcountry,usrprovince=@usrprovince,usrcity=@usrcity,smoke=@smoke,bed=@bed,pillow=@pillow,usrstatus=@usrstatus,ademail=@ademail,admobile=@admobile where uid=" + Uid + "";
            else
                SqlStr = "";
            if (ExecSql.SqlExecNoquery(SqlStr, pam) > 0)
                return true;
            else
                return false;
        }
        #endregion

        #region 常客信息添加、修改
        /// <summary>
        /// 修改或添加常客信息
        /// </summary>
        /// <returns></returns>
        public bool CardUser_Add(string Orgid, string Orgtype, string Unumber, string Lvlid, string Endyear, string Scores, string Usrname, string Usrpassword, string Usremail, string Emailformat, string Emaillanguage, string Usrmobile, string Usrfirstname, string Usrlastname, string Usrgender, string Usrbirthday, string Usrnationality, string Idtype, string Idno, string Usrcountry, string Usrprovince, string Usrcity, string Smoke, string Bed, string Pillow, string Regdate, string Logdate, string Logip, string Logcount, string Usrstatus, string Uid)
        {
            SqlParameter[] pam = new SqlParameter[30];
            pam[0] = new SqlParameter("@orgid", Orgid);
            pam[1] = new SqlParameter("@orgtype", Orgtype);
            pam[2] = new SqlParameter("@unumber", Unumber);
            pam[3] = new SqlParameter("@lvlid", Lvlid);
            pam[4] = new SqlParameter("@endyear", Endyear);
            pam[5] = new SqlParameter("@scores", Scores);
            pam[6] = new SqlParameter("@usrname", Usrname);
            pam[7] = new SqlParameter("@usrpassword", Usrpassword);
            pam[8] = new SqlParameter("@usremail", Usremail);
            pam[9] = new SqlParameter("@emailformat", Emailformat);
            pam[10] = new SqlParameter("@emaillanguage", Emaillanguage);
            pam[11] = new SqlParameter("@usrmobile", Usrmobile);
            pam[12] = new SqlParameter("@usrfirstname", Usrfirstname);
            pam[13] = new SqlParameter("@usrlastname", Usrlastname);
            pam[14] = new SqlParameter("@usrgender", Usrgender);
            pam[15] = new SqlParameter("@usrbirthday", Usrbirthday);
            pam[16] = new SqlParameter("@usrnationality", Usrnationality);
            pam[17] = new SqlParameter("@idtype", Idtype);
            pam[18] = new SqlParameter("@idno", Idno);
            pam[19] = new SqlParameter("@usrcountry", Usrcountry);
            pam[20] = new SqlParameter("@usrprovince", Usrprovince);
            pam[21] = new SqlParameter("@usrcity", Usrcity);
            pam[22] = new SqlParameter("@smoke", Smoke);
            pam[23] = new SqlParameter("@bed", Bed);
            pam[24] = new SqlParameter("@pillow", Pillow);
            pam[25] = new SqlParameter("@regdate", Regdate);
            pam[26] = new SqlParameter("@logdate", Logdate);
            pam[27] = new SqlParameter("@logip", Logip);
            pam[28] = new SqlParameter("@logcount", Logcount);
            pam[29] = new SqlParameter("@usrstatus", Usrstatus);

            if (Uid != "")
                SqlStr = "update hfg_user set orgid=@orgid,orgtype=@orgtype,unumber=@unumber,lvlid=@lvlid,endyear=@endyear,scores=@scores,usrname=@usrname,usrpassword=@usrpassword,usremail=@usremail,emailformat=@emailformat,emaillanguage=@emaillanguage,usrmobile=@usrmobile,usrfirstname=@usrfirstname,usrlastname=@usrlastname,usrgender=@usrgender,usrbirthday=@usrbirthday,usrnationality=@usrnationality,idtype=@idtype,idno=@idno,usrcountry=@usrcountry,usrprovince=@usrprovince,usrcity=@usrcity,smoke=@smoke,bed=@bed,pillow=@pillow,regdate=@regdate,logdate=@logdate,logip=@logip,logcount=@logcount,usrstatus=@usrstatus where uid=" + Uid + "";
            else
                SqlStr = "";
            if (ExecSql.SqlExecNoquery(SqlStr, pam) > 0)
                return true;
            else
                return false;
        }
        #endregion

        #region 常客信息的删除
        public bool CardUser_Del(string Uid)
        {
            string sql = "", msg = "";
            sql = "delete from hfg_user where uid=" + Uid;
            if (ExecSql.SqlExecNoquery(sql) > 0)
                return true;
            else
                return false;
        }
        #endregion

        #region 常客用户表的用户名的添加或者修改
        /// <summary>
        /// 添加或者修改常客用户表的用户名
        /// </summary>
        /// <returns></returns>
        public bool CardUserUserName_Add(string UserName, string Gid, string Uid, string Userid)
        {
            SqlParameter[] pam = new SqlParameter[3];
            pam[0] = new SqlParameter("@username", UserName);
            pam[1] = new SqlParameter("@gid", Gid);
            pam[2] = new SqlParameter("@uid", Uid);

            if (Userid != "")
                SqlStr = "update hfg_user_username set username=@username,gid=@gid,uid=@uid where userid=" + Userid + "";
            else
                SqlStr = "insert into hfg_user_username(username,gid,uid) values(@username,@gid,@uid)";

            if (ExecSql.SqlExecNoquery(SqlStr, pam) > 0)
                return true;
            else
                return false;
        }
        #endregion

        #region 常客用户表的邮件的添加或者修改
        /// <summary>
        /// 添加或者修改常客用户表的邮件
        /// </summary>
        /// <returns></returns>
        public bool CardUserEmail_Add(string Email, string Gid, string Uid, string Eid)
        {
            SqlParameter[] pam = new SqlParameter[3];
            pam[0] = new SqlParameter("@email", Email);
            pam[1] = new SqlParameter("@gid", Gid);
            pam[2] = new SqlParameter("@uid", Uid);

            if (Eid != "")
                SqlStr = "update hfg_user_email set email=@email,gid=@gid,uid=@uid where eid=" + Eid + ";";
            else
                SqlStr = "insert into hfg_user_email(email,gid,uid) values(@email,@gid,@uid);";
            SqlStr = SqlStr + "update hfg_user set usremail=@email where uid=" + Uid;
            if (ExecSql.SqlExecNoquery(SqlStr, pam) > 0)
                return true;
            else
                return false;
        }
        #endregion

        #region 常客用户表的手机号码的添加或者修改
        /// <summary>
        /// 添加或者修改常客用户表的手机号码
        /// </summary>
        /// <returns></returns>
        public bool CardUserMobile_Add(string Mobnumb, string Gid, string Uid, string Mobid)
        {
            SqlParameter[] pam = new SqlParameter[3];
            pam[0] = new SqlParameter("@mobnumb", Mobnumb);
            pam[1] = new SqlParameter("@gid", Gid);
            pam[2] = new SqlParameter("@uid", Uid);

            if (Mobid != "")
                SqlStr = "update hfg_user_mobile set mobnumb=@mobnumb,gid=@gid,uid=@uid where mobid=" + Mobid + ";";
            else
                SqlStr = "insert into hfg_user_mobile(mobnumb,gid,uid) values(@mobnumb,@gid,@uid);";
            SqlStr = SqlStr + "update hfg_user set usrmobile=@mobnumb where uid=" + Uid;
            if (ExecSql.SqlExecNoquery(SqlStr, pam) > 0)
                return true;
            else
                return false;
        }
        #endregion

        #region 常客用户表的密码初始化
        /// <summary>
        /// 添加或者修改常客用户表的手机号码
        /// </summary>
        /// <returns></returns>
        public bool User_InitPass(string Usrpassword, string Uid)
        {
            if (ExecSql.SqlExecNoquery("update hfg_user set usrpassword='" + Usrpassword + "' where uid=" + Uid + "") > 0)
                return true;
            else
                return false;
        }
        #endregion
        private string CreatePassword()
        {
            Random r = new Random();
            int rl = r.Next(10000, 99999);
            return rl.ToString();
        }
        


        /// <summary>
        /// 待升级用户列表
        /// </summary>
        /// <param name="stime">查询开始时间</param>
        /// <param name="etime">查询结束时间</param>
        /// <param name="orders">排序</param>
        /// <param name="cpage">页数</param>
        /// <returns></returns>
        public DataSet upgradelist(string gid, string stime, string etime, string orders, int cpage)
        {
            string searchqual = " gid= " + gid;
            if (stime != "")
                searchqual += " and uupdate>='" + stime + "'";
            if (etime != "")
                searchqual += " and uupdate<='" + etime + "'";
            DataSet ds = ExecSql.AddParamFrKeyField("hfg_view_userupgrade", "*", orders, "logid", CFun.PageSize, cpage, searchqual);
            return ds;
        }

        /// <summary>
        /// 删除待升级用户
        /// </summary>
        /// <param name="gid"></param>
        /// <param name="logid">带升级用户编号组,逗号隔开</param>
        /// <returns>成功数量</returns>
        public int upgradedel(string gid, string logid)
        {
            if (logid.Substring(0, 1) == ",")
                logid = logid.Substring(1, logid.Length - 1);

            string sql = "delete from hfg_userup_templog where gid=" + gid + " and ";
            if (logid.IndexOf(',') >= 0)
                sql += "logid in (" + logid + ")";
            else
                sql += "logid=" + logid + "";

            return ExecSql.SqlExecNoquery(sql);
        }

        /// <summary>
        /// 处理升级
        /// </summary>
        /// <param name="gid"></param>
        /// <param name="logid">待升级用户编号</param>
        /// <returns>升级是否成功</returns>
        public DataRow userupgrade(string gid, string logid)
        {
            string sql = "hfg_userupgrade";
            SqlParameter[] pam = new SqlParameter[2];
            pam[0] = new SqlParameter("@logid", logid);
            pam[1] = new SqlParameter("@gid", gid);
            DataSet ds = ExecSql.GetDataSetByStoredProcedure(sql, pam);

            if (ds.Tables.Count > 0)
            {
                //保存日志
                return ds.Tables[0].Rows[0];
            }
            else
                return null;
        }
    }
}
